import sqlite3
import os
import datetime

# 检查当前目录
def check_current_directory():
    print(f"当前工作目录: {os.getcwd()}")
    print("目录中的.db文件:")
    for file in os.listdir('.'):
        if file.endswith('.db'):
            file_path = os.path.join(os.getcwd(), file)
            size = os.path.getsize(file_path) / 1024  # KB
            mtime = datetime.datetime.fromtimestamp(os.path.getmtime(file_path))
            print(f"- {file} ({size:.2f} KB, 修改时间: {mtime})")

# 重新创建正确的trade_records表结构
def recreate_trade_records_table():
    try:
        # 备份现有数据库
        backup_db()
        
        # 连接数据库
        conn = sqlite3.connect('trade_monitor.db')
        cursor = conn.cursor()
        
        # 检查是否已有trade_records表
        cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='trade_records';")
        table_exists = cursor.fetchone() is not None
        
        if table_exists:
            # 重命名现有表以便保留数据
            cursor.execute("ALTER TABLE trade_records RENAME TO trade_records_old;")
            print("已重命名现有表为trade_records_old")
        
        # 创建新的trade_records表，使用正确的结构
        create_table_sql = '''
        CREATE TABLE IF NOT EXISTS trade_records (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            product_name TEXT NOT NULL,
            direction TEXT NOT NULL,
            offset TEXT NOT NULL,
            volume REAL NOT NULL,
            price REAL NOT NULL,
            reason TEXT NOT NULL,
            timestamp TEXT NOT NULL,
            created_at TEXT NOT NULL
        );
        '''
        cursor.execute(create_table_sql)
        print("已创建正确结构的trade_records表")
        
        # 创建索引以提高查询性能
        cursor.execute("CREATE INDEX IF NOT EXISTS idx_trade_records_product ON trade_records(product_name);")
        cursor.execute("CREATE INDEX IF NOT EXISTS idx_trade_records_timestamp ON trade_records(timestamp);")
        cursor.execute("CREATE INDEX IF NOT EXISTS idx_trade_records_direction ON trade_records(direction);")
        print("已创建索引")
        
        # 如果有旧表数据，尝试迁移
        try:
            cursor.execute("SELECT COUNT(*) FROM trade_records_old;")
            old_count = cursor.fetchone()[0]
            
            if old_count > 0:
                print(f"发现{old_count}条旧数据，尝试迁移...")
                # 由于表结构差异很大，这里我们只迁移部分字段
                cursor.execute('''
                    INSERT INTO trade_records (product_name, direction, offset, volume, price, reason, timestamp, created_at)
                    SELECT product_name, direction, action_type, volume, price, remark, trade_time, trade_time
                    FROM trade_records_old;
                ''')
                print("数据迁移完成")
        except Exception as e:
            print(f"数据迁移过程中出错: {str(e)}")
        
        # 提交事务
        conn.commit()
        
    except Exception as e:
        print(f"重新创建表结构时出错: {str(e)}")
        # 如果出错，回滚
        conn.rollback()
    finally:
        if conn:
            conn.close()

# 备份数据库
def backup_db():
    try:
        backup_time = datetime.datetime.now().strftime('%Y%m%d_%H%M%S')
        backup_path = f'trade_monitor_backup_{backup_time}.db'
        
        if os.path.exists('trade_monitor.db'):
            # 复制文件作为备份
            with open('trade_monitor.db', 'rb') as f_in:
                with open(backup_path, 'wb') as f_out:
                    f_out.write(f_in.read())
            print(f"数据库已备份到: {backup_path}")
    except Exception as e:
        print(f"备份数据库时出错: {str(e)}")

# 更新insert_trade_record函数的实现
def update_insert_trade_record_functions():
    try:
        # 查找所有包含insert_trade_record函数的Python文件
        python_files = [f for f in os.listdir('.') if f.endswith('.py')]
        
        updated_files = 0
        
        for file_path in python_files:
            try:
                # 读取文件内容
                with open(file_path, 'r', encoding='utf-8', errors='ignore') as f:
                    content = f.read()
                
                # 检查文件是否包含insert_trade_record函数
                if 'def insert_trade_record' in content:
                    # 备份原文件
                    backup_path = file_path + '.bak'
                    with open(backup_path, 'w', encoding='utf-8') as f:
                        f.write(content)
                    
                    # 提取函数定义
                    start_index = content.find('def insert_trade_record')
                    end_index = content.find('def ', start_index + 1)
                    if end_index == -1:
                        end_index = len(content)
                    
                    # 创建新的函数实现
                    new_function = """def insert_trade_record(symbol, direction, offset, volume, price, reason):
    try:
        # 获取当前时间
        current_time = time.strftime('%Y-%m-%d %X')
        
        # 连接到SQLite数据库
        conn = sqlite3.connect('trade_monitor.db')
        cursor = conn.cursor()
        
        # 参数化插入SQL语句
        insert_sql = """
            INSERT INTO trade_records (product_name, direction, offset, volume, price, reason, timestamp, created_at)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?)
        """
        
        # 执行插入
        cursor.execute(insert_sql, (
            symbol, direction, offset, volume, price, reason, current_time, current_time
        ))
        
        # 提交事务
        conn.commit()
        
        # 关闭连接
        conn.close()
        
        # 打印成功信息到控制台
        console.print(f"交易记录已成功插入数据库: {symbol}, {direction}, {offset}, {volume}, {price}", style="success")
        
    except Exception as e:
        console.print(f"记录交易到数据库时出错: {e}", style="error")
        logging.error(f"插入交易记录失败: {str(e)}")"""
                    
                    # 替换旧函数
                    new_content = content[:start_index] + new_function + content[end_index:]
                    
                    # 保存更新后的文件
                    with open(file_path, 'w', encoding='utf-8') as f:
                        f.write(new_content)
                    
                    print(f"已更新文件: {file_path}")
                    updated_files += 1
            except Exception as e:
                print(f"处理文件 {file_path} 时出错: {str(e)}")
        
        print(f"总共更新了 {updated_files} 个文件中的insert_trade_record函数")
    except Exception as e:
        print(f"更新函数实现时出错: {str(e)}")

# 测试修复后的功能
def test_fixed_functionality():
    try:
        # 连接数据库
        conn = sqlite3.connect('trade_monitor.db')
        cursor = conn.cursor()
        
        # 插入一条测试记录
        test_symbol = "TEST.FIXED"
        current_time = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        
        cursor.execute(
            "INSERT INTO trade_records (product_name, direction, offset, volume, price, reason, timestamp, created_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
            (test_symbol, "BUY", "OPEN", 1.0, 1000.0, "测试修复", current_time, current_time)
        )
        conn.commit()
        
        # 验证插入是否成功
        cursor.execute("SELECT COUNT(*) FROM trade_records WHERE product_name = ?", (test_symbol,))
        count = cursor.fetchone()[0]
        
        if count > 0:
            print(f"测试成功！记录已成功插入到修复后的trade_records表。")
            
            # 显示表中的记录数量
            cursor.execute("SELECT COUNT(*) FROM trade_records")
            total_count = cursor.fetchone()[0]
            print(f"修复后的trade_records表总记录数: {total_count}")
            
            # 显示最新的3条记录
            cursor.execute("SELECT * FROM trade_records ORDER BY id DESC LIMIT 3")
            latest_records = cursor.fetchall()
            print("最新的3条记录:")
            for record in latest_records:
                print(f"  {record}")
        else:
            print("测试失败！记录未成功插入到修复后的trade_records表。")
            
    except Exception as e:
        print(f"测试过程中出错: {str(e)}")
    finally:
        if conn:
            conn.close()

if __name__ == "__main__":
    print("=== 交易记录数据库完整修复工具 ===")
    
    # 1. 检查当前目录
    check_current_directory()
    
    # 2. 重新创建正确的表结构
    recreate_trade_records_table()
    
    # 3. 更新函数实现
    update_insert_trade_record_functions()
    
    # 4. 测试修复后的功能
    test_fixed_functionality()
    
    print("\n=== 修复完成 ===")
    print("1. 数据库表结构已更新为正确格式")
    print("2. 所有程序中的insert_trade_record函数已更新")
    print("3. 数据库已备份，可在需要时恢复")
    print("\n建议：运行主程序并观察交易记录是否正常录入")